SQL Query

Executing SQL commands to manipulate and retrieve data from relational databases efficiently.

---

🧩 Overview

SQL Queries are used to interact with relational databases to:

• Retrieve data using SELECT
• Modify data using INSERT, UPDATE, or DELETE
• Structure data using JOIN, GROUP BY, ORDER BY, etc.
• Perform filtering, aggregation, and transformation of datasets

This workflow enables powerful, flexible data access and manipulation with precision and efficiency.

---

📄 Types of SQL Queries

1. Data Retrieval

SELECT * FROM patients WHERE status = 'active';

2. Data Insertion

INSERT INTO patients (name, dob, gender) VALUES ('Jane Doe', '1990-05-12', 'Female');

3. Data Update

UPDATE patients SET mobile = '9998887770' WHERE id = 101;

4. Data Deletion

DELETE FROM patients WHERE id = 101;

---

🔄 Query Components

Component	Description
SELECT	Specifies the columns to retrieve
FROM	Identifies the source table(s)
WHERE	Filters records based on conditions
JOIN	Combines rows from two or more tables
GROUP BY	Aggregates data into groups
ORDER BY	Sorts the result set
LIMIT/OFFSET	Restricts number of records retrieved

---

🔍 Example: Complex SELECT Query

SELECT
    p.name, p.mobile, l.test_name, r.result_value
FROM
    patients p
JOIN
    lab_results r ON p.id = r.patient_id
JOIN
    lab_tests l ON r.test_id = l.id
WHERE
    r.result_date >= '2025-01-01'
ORDER BY
    r.result_date DESC;

---

🛡️ Security Practices

• Use parameterized queries to prevent SQL injection:

  const sql = "SELECT * FROM users WHERE username = ?";
  db.query(sql, [username], callback);
• Restrict query access based on user roles
• Sanitize user inputs
• Use views for sensitive data exposure

---

🧪 SQL Query Testing

• Use tools like MySQL Workbench, pgAdmin, DataGrip, or terminal CLI
• Test with edge-case data
• Validate:
  • Result count
  • Column types
  • Response time

---

📈 Query Optimization Tips

• Use proper indexes on frequently filtered or joined columns
• Avoid SELECT * in production
• Use EXPLAIN or QUERY PLAN to understand performance
• Avoid nested subqueries unless necessary
• Reduce joins by normalizing or caching common joins

---

📂 Execution Contexts

SQL Queries can be executed from:

• Database clients (e.g., DBeaver, HeidiSQL)
• API endpoints (via ORM or direct SQL)
• Stored Procedures
• Workflow engines
• Backend jobs or scripts

---

🔧 Common Use Cases

Use Case	Query Type
Load active patient list	SELECT
Add lab test result	INSERT
Correct patient contact	UPDATE
Remove duplicate record	DELETE
Generate monthly report	SELECT + GROUP BY
Lookup patient tests in bulk	SELECT + JOIN

---

🔚 Summary

SQL Queries offer fine-grained control over your data. By structuring queries correctly, following security best practices, and optimizing performance, they form the foundation of robust data workflows.

---